﻿///下面两个编译条件参数指定产生报表数据的格式。如果都不定义，则产生 XML 形式的报表数据
///编译条件参数定义在项目属性的“生成->条件编译符号”里更合适，这样可以为整个项目使用
///_XML_REPORT_DATA：指定产生 XML 形式的报表数据
///_JSON_REPORT_DATA：指定产生 JSON 形式的报表数据。
//#define _XML_REPORT_DATA
#define _JSON_REPORT_DATA

using System;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Text;
using System.Diagnostics;

namespace GridReport
{

#if _JSON_REPORT_DATA
    using MyDbReportData = DatabaseJsonReportData;
#else
    using MyDbReportData = DatabaseXmlReportData;
#endif

    /// <summary>
    /// 在这里集中产生整个项目的所有报表需要的 XML 或 JSON 文本数据 
    /// </summary>
    public class DataTextProvider
    {
        //创建一个静态的DataTextProvider对象,在构造函数里执行初始化任务
        private static DataTextProvider initStartup = new DataTextProvider();

        public DataTextProvider()
        {
            DataTextProvider.InitDataFunMap();
        }

        /// <summary>
        /// 根据查询SQL语句产生报表数据
        /// </summary>
        public static string Build(string QuerySQL)
        {
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        /// <summary>
        /// 根据多条查询SQL语句产生报表数据，数据对应多记录集
        /// </summary>
        public static string Build(ArrayList QueryList)
        {
            return MyDbReportData.TextFromMultiSQL(QueryList);
        }

        #region 产生特定的报表数据文本
        public static string Customer()
        {
            return MyDbReportData.TextFromOneSQL("select * from Customers order by Region,City");
        }

        public static string AppendBlankRow()
        {
            string QuerySQL = string.Format(
                "select m.OrderId, m.OrderDate, d.Productid,p.ProductName,d.Quantity," +
                "d.UnitPrice*d.Quantity as Amount " +
                "from orders m inner join (OrderDetails d inner join Products p on d.ProductID=p.ProductID) " +
                "on m.orderid=d.orderid " +
                "where (m.OrderDate between {0}1996-1-1{0} And {0}1997-9-30{0}) and d.Productid<10 " +
                "order by d.ProductID", DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string Categories()
        {
            return MyDbReportData.TextFromOneSQL("select * from categories");
        }

        public static string ContractOne()
        {
            const string QuerySQL =
                "select m.OrderID,m.CustomerId,c.CompanyName,m.OrderDate, "
                + "p.ProductName,d.UnitPrice,d.Quantity,d.UnitPrice*d.Quantity as Amount "
                + "from (Orders m inner join "
                + "(OrderDetails as d inner join Products p on P.ProductID=D.ProductID) on m.OrderId=d.OrderId) "
                + "left join Customers c on c.CustomerID=m.CustomerID "
                + "where m.OrderID=10252 and d.ProductID=20 "
                + "order by m.OrderDate, m.OrderID";

            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string CrossTab()
        {
            const string QuerySQL = "select c.City,m.CustomerId,c.CompanyName,d.ProductID,p.ProductName," +
                "d.Quantity, d.UnitPrice*d.Quantity as Amount " +
                "from (Orders m inner join " +
                "(OrderDetails as d inner join Products p " +
                "on P.ProductID=D.ProductID) on m.OrderId=d.OrderId) " +
                "left join Customers c on c.CustomerID=m.CustomerID " +
                "where d.ProductID<8 " +
                "order by c.City,m.CustomerId, d.ProductID";

            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string CrossTabByDay()
        {
            string QuerySQL = string.Format("select c.CompanyName,m.OrderDate,d.UnitPrice*d.Quantity as Amount " +
                "from (Orders m inner join OrderDetails as d on m.OrderId=d.OrderId) " +
                "left join Customers c on c.CustomerID=m.CustomerID " +
                "where m.OrderDate between {0}1997-6-1{0} and {0}1997-7-15{0} " +
                "order by c.CompanyName, m.OrderDate", DatabaseReportData.DateSqlBracketChar);

            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string CrossTabByMonth()
        {
            string QuerySQL = string.Format("select c.CompanyName,m.OrderDate,d.UnitPrice*d.Quantity as Amount " +
                "from (Orders m inner join OrderDetails as d on m.OrderId=d.OrderId) " +
                "left join Customers c on c.CustomerID=m.CustomerID " +
                "where m.OrderDate between {0}1997-1-1{0} and {0}1997-12-31{0} " +
                "order by c.CompanyName, m.OrderDate", DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string CrossTabCalendar()
        {
            string QuerySQL = string.Format(
                "select m.OrderDate,sum(d.Quantity) as Qty,sum(d.UnitPrice*d.Quantity) as Amount " +
                "from (Orders m inner join OrderDetails as d on m.OrderId=d.OrderId) " +
                "where m.OrderDate between {0}1997-1-1{0} and {0}1997-12-31{0} " +
                "group by m.OrderDate " +
                "order by m.OrderDate", DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string CrossTabSubtotal()
        {
            string QuerySQL = string.Format(
                "select t.CategoryName, p.ProductName,c.City,c.CompanyName,d.Quantity " +
                "from (Orders m inner join  " +
                "(OrderDetails as d inner join (Products p inner join Categories t on p.CategoryID=t.CategoryID) " +
                "on P.ProductID=D.ProductID) on m.OrderId=d.OrderId) " +
                "left join Customers c on c.CustomerID=m.CustomerID " +
                "where m.OrderDate between {0}1997-1-1{0} and {0}1997-3-31{0} " +
                "order by t.CategoryName,p.ProductName,c.City,c.CompanyName ", DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string CrossTabYearMonth()
        {
            string QuerySQL = "select Year(m.OrderDate) As TheYear,Month(m.OrderDate) As TheMonth, sum(d.UnitPrice*d.Quantity) as Amount " +
                "from Orders m inner join OrderDetails as d on m.OrderId=d.OrderId " +
                "group by Year(m.OrderDate),Month(m.OrderDate) " +
                "order by Year(m.OrderDate),Month(m.OrderDate)";
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string EmployeeOne()
        {
            const string QuerySQL = "select * from Employees where EmployeeID=5";
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string FreeGridwithDetailGrid()
        {
            //第一个查询SQL串指定报表明细数据的查询SQL
            //第二个查询SQL串指定报表参数数据的查询SQL
            const string RecordsetQuerySQL = "select * from Employees where EmployeeID<8";
            const string ParameterQuerySQL = "select * from Employees where EmployeeID=8";

            ArrayList QueryList = new ArrayList();
            QueryList.Add(new ReportQueryItem(RecordsetQuerySQL, "Detail"));
            QueryList.Add(new ReportQueryItem(ParameterQuerySQL, "FreeGrid"));

            return MyDbReportData.TextFromMultiSQL(QueryList);
        }

        public static string InvoiceMany()
        {
            const string QuerySQL =
                "select m.OrderID,m.CustomerId,c.CompanyName,C.Address,m.OrderDate,c.ContactName+c.Phone as Remark, " +
                "d.ProductID,p.ProductName,d.UnitPrice,d.Quantity,d.UnitPrice*d.Quantity as Amount " +
                "from (Orders m inner join (OrderDetails d inner join Products p on p.ProductID=d.ProductID) " +
                "on d.OrderID=m.OrderID) left join Customers c on c.CustomerID=m.CustomerID " +
                "where m.OrderID>=10255 and m.OrderID<10260 ";
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string InvoiceOne()
        {
            //第一个查询SQL串指定报表明细数据的查询SQL
            //第二个查询SQL串指定报表参数数据的查询SQL
            const string RecordsetQuerySQL = "select d.ProductID,p.ProductName,d.UnitPrice,d.Quantity,d.UnitPrice*d.Quantity as Amount "
                + "from OrderDetails as d inner join Products p on P.ProductID=D.ProductID "
                + "where d.OrderID=10255";
            const string ParameterQuerySQL = "select m.OrderID,m.CustomerId,c.CompanyName,C.Address,m.OrderDate,c.ContactName+c.Phone as Remark "
                + "from Orders m left join Customers c on c.CustomerID=m.CustomerID "
                + "where m.OrderID=10255";

            ArrayList QueryList = new ArrayList();
            QueryList.Add(new ReportQueryItem(RecordsetQuerySQL, "Detail"));
            QueryList.Add(new ReportQueryItem(ParameterQuerySQL, "Master"));

            return MyDbReportData.TextFromMultiSQL(QueryList);
        }

        public static string Picture()
        {
            const string QuerySQL = "select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate," +
                "Address,City,Region,PostalCode,Country,HomePhone,Extension,Photo,Notes from Employees";

            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string RTFSample()
        {
            string QuerySQL = string.Format("select m.OrderID,m.CustomerId,c.CompanyName,c.ContactName,c.Address,c.city,c.Region,c.Country,c.Postalcode," +
                "m.OrderDate,M.Freight,d.ProductID,p.ProductName," +
                "d.UnitPrice,d.Quantity,d.Discount," +
                "d.UnitPrice*d.Quantity as Amount," +
                "d.UnitPrice*d.Quantity*d.Discount as DiscountAmt," +
                "d.UnitPrice*d.Quantity-d.UnitPrice*d.Quantity*d.Discount as NetAmount " +
                "from (Orders m inner join " +
                "(OrderDetails as d inner join Products p on P.ProductID=D.ProductID) on m.OrderId=d.OrderId) " +
                "left join Customers c on c.CustomerID=m.CustomerID " +
                "where m.OrderDate between {0}1997-1-1{0} And {0}1997-1-15{0} " +
                "order by m.CustomerID,m.OrderDate, m.OrderID", DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string SaleByProduct()
        {
            string QuerySQL = string.Format(
                "select m.OrderID,m.OrderDate, " +
                "d.ProductID,p.ProductName,d.UnitPrice,d.Quantity,d.UnitPrice*d.Quantity as Amount  " +
                "from Orders m inner join  " +
                "(OrderDetails as d inner join Products p on P.ProductID=D.ProductID) on m.OrderId=d.OrderId " +
                "where m.OrderDate between {0}1997-6-1{0} and {0}1997-12-31{0} " +
                "order by d.ProductID, m.OrderDate", DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string SaleDetail()
        {
            const string QuerySQL = "select m.OrderID,m.CustomerId,c.CompanyName,m.OrderDate,M.Freight," +
                "d.ProductID,p.ProductName,d.UnitPrice,d.Quantity,d.Discount, " +
                "d.UnitPrice*d.Quantity as Amount, d.UnitPrice*d.Quantity*d.Discount as DiscountAmt, " +
                "d.UnitPrice*d.Quantity-d.UnitPrice*d.Quantity*d.Discount as NetAmount " +
                "from (Orders m inner join " +
                "(OrderDetails as d inner join Products p on P.ProductID=D.ProductID) on m.OrderId=d.OrderId) " +
                "left join Customers c on c.CustomerID=m.CustomerID " +
                "where m.OrderID<=10300 " +
                "order by m.OrderDate, m.OrderID";
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string SaleSumByProduct()
        {
            string QuerySQL = string.Format(
                "select d.Productid,p.ProductName,sum(d.Quantity) as Quantity, " +
                "sum(d.UnitPrice*d.Quantity*(1-d.Discount)) as Amount " +
                "from orders m inner join (OrderDetails d inner join Products p " +
                "on d.ProductID=p.ProductID) " +
                "on m.orderid=d.orderid " +
                "where m.OrderDate between {0}1997-1-1{0} and {0}1997-12-31{0} " +
                "group by d.Productid,p.ProductName " +
                "order by d.Productid", DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string SubReport_4a()
        {
            const string Top10CustomerQuerySQL =
                "select top 10 c.CustomerID, c.CompanyName, sum(o.Quantity*o.UnitPrice) As SumAmt " +
                "from OrderDetails o, Orders m, customers c " +
                "where o.OrderID=m.OrderID and m.CustomerID=c.CustomerID " +
                "group by c.CustomerID, c.CompanyName " +
                "order by sum(o.Quantity*o.UnitPrice) desc";
            const string Top10ProductQuerySQL =
                "select top 10 p.ProductID, p.ProductName, sum(o.Quantity*o.UnitPrice) As SumQty " +
                "from OrderDetails o, Products p " +
                "where o.ProductID=p.ProductID " +
                "group by p.ProductID, p.ProductName " +
                "order by sum(Quantity*o.UnitPrice) desc";

            ArrayList QueryList = new ArrayList();
            QueryList.Add(new ReportQueryItem("select * from Customers order by CustomerID", "Customer"));
            QueryList.Add(new ReportQueryItem("select * from Products order by ProductName", "Product"));
            QueryList.Add(new ReportQueryItem(Top10CustomerQuerySQL, "Top10Customer"));
            QueryList.Add(new ReportQueryItem(Top10ProductQuerySQL, "Top10Product"));
            return MyDbReportData.TextFromMultiSQL(QueryList);
        }

        public static string SubReport_4b()
        {
            ArrayList QueryList = new ArrayList();
            QueryList.Add(new ReportQueryItem("select * from Customers order by CustomerID", "Customer"));
            QueryList.Add(new ReportQueryItem("select * from Products order by ProductName", "Product"));
            QueryList.Add(new ReportQueryItem("select * from Customers order by CustomerID", "Customer2"));
            return MyDbReportData.TextFromMultiSQL(QueryList);
        }

        public static string SubReport_4c()
        {
            const string RelateCustomerQuerySQL =
                "select o.OrderID, o.ShipCity, c.* from Customers c, Orders o " +
                "where OrderID<=10260 and c.City=o.ShipCity " +
                "order by o.OrderID";

            ArrayList QueryList = new ArrayList();
            QueryList.Add(new ReportQueryItem("select * from Orders where OrderID<=10260 order by OrderID", "Master"));
            QueryList.Add(new ReportQueryItem("select * from OrderDetails where OrderID<=10260", "Detail1"));
            QueryList.Add(new ReportQueryItem(RelateCustomerQuerySQL, "Detail2"));

            return MyDbReportData.TextFromMultiSQL(QueryList);
        }

        public static string SubReport_4d(string city)
        {
            string CustomerQuerySQL = "select * from Customers where City='" + city + "'";
            string SupplierQuerySQL = "select * from Suppliers where City='" + city + "'";

            ArrayList QueryList = new ArrayList();
            QueryList.Add(new ReportQueryItem(CustomerQuerySQL, "Customer"));
            QueryList.Add(new ReportQueryItem(SupplierQuerySQL, "Supplier"));

            return MyDbReportData.TextFromMultiSQL(QueryList);
        }

        public static string Report_7_3g(int BeginNo, int EndNo)
        {
            string QuerySQL = string.Format("select * from Products " +
                "where ProductID>={0} and ProductID<={1} " +
                "order by ProductID", BeginNo, EndNo);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string Chart_8b()
        {
            ArrayList QueryList = new ArrayList();
            QueryList.Add(new ReportQueryItem("select * from scatter order by Name, X", "Table1"));
            QueryList.Add(new ReportQueryItem("select * from scatter order by Name, X", "Table2"));
            QueryList.Add(new ReportQueryItem("select * from scatter order by Name, X", "Table3"));
            QueryList.Add(new ReportQueryItem("select * from scatter order by Name, X", "Table4"));
            return MyDbReportData.TextFromMultiSQL(QueryList);
        }

        public static string Chart_8d()
        {
            string SQL = "select c.Region,  d.ProductID,p.ProductName, " +
                "sum(d.UnitPrice * d.Quantity) as Amount " +
                "from(Orders m inner join (OrderDetails as d inner join Products p on P.ProductID = D.ProductID) on m.OrderId = d.OrderId) " +
                "left join Customers c on c.CustomerID = m.CustomerID " +
                "where d.ProductID in (1, 10, 11, 21) and m.OrderDate between #1997/1/1# and #1997/12/31# " +
                "group by c.Region, d.ProductID, p.ProductName " +
                "order by d.ProductID, c.Region";
            string SQL2 = "select c.Region, sum(d.UnitPrice * d.Quantity) as Amount, sum(d.Quantity) as Quantity " +
                "from(Orders m inner join OrderDetails d on m.OrderId = d.OrderId) " +
                "left join Customers c on c.CustomerID = m.CustomerID " +
                "where d.ProductID = 11 and m.OrderDate between #1997/1/1# and #1997/12/31# " +
                "group by c.Region " +
                "order by c.Region";

            ArrayList QueryList = new ArrayList();
            QueryList.Add(new ReportQueryItem(SQL, "Table1"));
            QueryList.Add(new ReportQueryItem(SQL, "Table2"));
            QueryList.Add(new ReportQueryItem(SQL, "Table3"));
            QueryList.Add(new ReportQueryItem(SQL2, "Table4"));
            return MyDbReportData.TextFromMultiSQL(QueryList);
        }

        public static string FilterSaleSummary(string BeginDate, string EndDate)
        {
            string QuerySQL = string.Format("select p.CategoryID,c.CategoryName,d.Productid,p.ProductName," +
            "sum(d.Quantity) as Quantity,sum(d.UnitPrice*d.Quantity) as Amount " +
            "from orders m inner join (OrderDetails d inner join (Products p inner join Categories c on c.CategoryID=p.CategoryID) " +
            "on d.ProductID=p.ProductID) on m.orderid=d.orderid " +
            "where m.OrderDate between {2}{0}{2} And {2}{1}{2}" +
            "group by p.CategoryID,c.CategoryName,d.Productid,p.ProductName " +
            "order by p.CategoryID,d.Productid", BeginDate, EndDate, DatabaseReportData.DateSqlBracketChar);

            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string FilterCustomerProductCrosstab(string BeginDate, string EndDate)
        {
            string QuerySQL = string.Format(
                "select p.CategoryID,t.CategoryName,p.ProductID,p.ProductName,c.City,c.CompanyName,d.Quantity " +
                "from (Orders m inner join  " +
                "(OrderDetails as d inner join (Products p inner join Categories t on p.CategoryID=t.CategoryID) " +
                "on p.ProductID=D.ProductID) on m.OrderId=d.OrderId) " +
                "left join Customers c on c.CustomerID=m.CustomerID " +
                "where m.OrderDate between {2}{0}{2} And {2}{1}{2} " +
                "order by t.CategoryName,p.ProductName,c.City,c.CompanyName ", BeginDate, EndDate, DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string FilterCrossPeriod(string BeginDate, string EndDate)
        {
            string QuerySQL = string.Format(
                "select d.ProductID,p.ProductName,m.OrderDate,d.UnitPrice*d.Quantity as Amount " +
                "from (Orders m inner join OrderDetails as d on m.OrderId=d.OrderId) " +
                "left join Products p on d.ProductID=p.ProductID " +
                "where m.OrderDate between {2}{0}{2} And {2}{1}{2} " +
                "order by p.ProductName, m.OrderDate", BeginDate, EndDate, DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string FilterChartBar(string BeginDate, string EndDate)
        {
            string QuerySQL = string.Format(
                "select p.CategoryID,t.CategoryName, c.City, sum(d.UnitPrice*d.Quantity) as Amount " +
                "from (Orders m inner join " +
                "(OrderDetails as d inner join (Products p inner join Categories t on p.CategoryID=t.CategoryID) " +
                "on P.ProductID=D.ProductID) on m.OrderId=d.OrderId) " +
                "left join Customers c on c.CustomerID=m.CustomerID " +
                "where m.OrderDate between {2}{0}{2} and {2}{1}{2} " +
                "group by p.CategoryID,t.CategoryName, c.City " +
                "order by c.City,t.CategoryName", BeginDate, EndDate, DatabaseReportData.DateSqlBracketChar);
            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        public static string FilterSaleDetail(string BeginDate, string EndDate, string CategoryID, string ProductID, string City, string CompanyName)
        {
            string KeyFilter = "";
            if (CategoryID != null)
                KeyFilter += " and p.CategoryID=" + CategoryID;
            if (ProductID != null)
                KeyFilter += " and d.Productid=" + ProductID;
            if (City != null)
                KeyFilter += " and c.City='" + City + "'";
            if (CompanyName != null)
                KeyFilter += " and c.CompanyName='" + CompanyName + "'";

            string QuerySQL = string.Format("select p.CategoryID,t.CategoryName,m.OrderId, m.OrderDate,c.City,c.CompanyName,d.Productid,p.ProductName,d.Quantity, " +
                "d.UnitPrice * d.Quantity as Amount " +
                "from (orders m inner join (OrderDetails d inner join " +
                "(Products p inner join Categories t on t.CategoryID = p.CategoryID) " +
                "on d.ProductID = p.ProductID) on m.orderid = d.orderid) " +
                "inner join Customers c on m.CustomerID = c.CustomerID " +
                "where (m.OrderDate between {3}{0}{3} And {3}{1}{3}) {2} " +
                "order by p.CategoryID,m.OrderDate ",
                BeginDate, EndDate, KeyFilter, DatabaseReportData.DateSqlBracketChar);

            return MyDbReportData.TextFromOneSQL(QuerySQL);
        }

        /// <summary>
        /// add by zfx 一丹体检报告sql
        /// </summary>
        /// <param name="v_tjbh"></param>
        /// <param name="v_tjcs"></param>
        /// <returns></returns>
        public static string FillTjbb(string v_tjbh,string v_tjcs)
        {
            //解密
            //v_tjbh=YDEncryption.EncryptionHelper.Decode(v_tjbh);
            v_tjbh=v_tjbh.Remove(5,1);
            v_tjbh=v_tjbh.Remove(0,2);
            
             //v_tjcs = YDEncryption.EncryptionHelper.Decode(v_tjcs);

             string SQL = string.Format(@"SELECT  t.id,T.SFZH,T.XM, G.GNAME,T.NL,T.TJBH,H.MC,T.ZS,T.JY,J.XM AS GDYS,T.GDRQ,
                                                                to_char(t.createtime,'YYYY')||'年'||to_char(t.createtime,'MM')||'月'||to_char(t.createtime,'DD')||'日' as riqi,
                                                                to_char(t.tjrq,'yyyy')||'年'||to_char(t.tjrq,'mm')||'月'||to_char(t.tjrq,'dd')||'日' as tjrq,
                                                                T.DJLSH,T.REPORTTAPE,T.RYBH,T.TJLB,X.NAME,X.JCRQ,X.JCYS,X.XMMC,X.RESULT,t.tjcs,
                                                                X.UNIT,X.CKZ,X.REMIND,X.DECTCTIONFINDING,t.zgl,t.jhgl,t.JCYHYS,z.grs,z.jw_jbname,
                                                                z.jw_zdrq,z.jw_zddw,z.jw_zg,z.yjs_ccnl,z.yjs_jq,z.yjs_zq,z.yjs_tjnl,z.sy_znrs,z.sy_lctc,z.sy_zctc,
                                                                z.sy_sctc,z.sy_yctc,z.xy_xyqk,z.xy_xyl,z.xy_yl,z.yj_yjqk,z.yj_jl,z.yj_wineyear,z.remarks,z.symptom,z.symptom2,h1.listname as h_yhys,
                                                                h2.listname as h_xyqk,h3.listname as h_yjqk,t.zjjl,h4.listname as zyjbname,t.phone as mobile,h5.listname as DUTYSTATE,
                                                                ym.name as hyzk    
                                                                  FROM YDTJ_TJ_TJDJB T
                                                                  LEFT JOIN YDTJ_GENDER G ON G.GCODE = T.XB AND T.DQCODE = G.DQCODE  AND T.HCODE = G.HCODE
                                                                  LEFT JOIN YDTJ_HYDWDMB H ON T.DWBH = H.BH  AND T.DQCODE = H.DQCODE  AND T.HCODE = H.HCODE
                                                                  LEFT JOIN YDTJ_GZRY J ON J.GKHM = T.GDYS  AND T.DQCODE = J.DQCODE  AND T.HCODE = J.HCODE
                                                                  LEFT JOIN (SELECT DISTINCT B.MC AS DEPART,Z.NAME,R.XM AS JCYS, P.DETECTIONDATE AS JCRQ, TO_CHAR(P.DECTCTIONFINDING) AS DECTCTIONFINDING,P.PEISITEMCODE AS ZHXMBH, M.MC AS XMMC, TO_CHAR(D.RESULT) AS RESULT, M.DW AS UNIT,
                                                                CASE NVL(RTRIM(LTRIM(D.CKZ)), '')
                                                                WHEN '' THEN
                                                                 (CASE T.XB
                                                                WHEN '1' THEN
                                                                 (CASE NVL(M.NXCKXX,'')
                                                                 WHEN '' THEN
                                                                 M.CKXX || '--' || M.CKSX
                                                                 ELSE
                                                                 M.NXCKXX || '--' || M.NXCKSX
                                                                END) ELSE(CASE NVL(M.CKXX, '')
                                                                WHEN '' THEN
                                                                 ''
                                                                ELSE
                                                                M.CKXX || '--' || M.CKSX
                                                                END) END) ELSE D.CKZ END AS CKZ,
                                                                 D.REMIND,B.DISP_ORDER as B_DISP_ORDER,Z.SHOWORDER,M.DISP_ORDER as M_DISP_ORDER,P.PEISITEMCODE,M.DISP_ORDER,P.DQCODE,P.HCODE,P.PEISCODE,P.TJCS
                                                                FROM YDTJ_PEISRECORD P
                                                                 INNER JOIN YDTJ_TJ_TJDJB T ON P.PEISCODE = T.TJBH  AND P.TJCS = T.TJCS AND T.DQCODE = P.DQCODE  AND T.HCODE = P.HCODE
                                                                LEFT JOIN YDTJ_TJ_TJLXB B ON P.DEPARTMENTCODE = B.LXBH   AND B.DQCODE = P.DQCODE  AND B.HCODE = P.HCODE
                                                                 LEFT JOIN YDTJ_ZHXM_HD Z ON P.PEISITEMCODE = Z.GROUPNO AND Z.DQCODE = P.DQCODE  AND Z.HCODE = P.HCODE
                                                                 LEFT JOIN YDTJ_GZRY R ON P.DETECTIONDOCTOR = R.GKHM  AND R.DQCODE = P.DQCODE  AND R.HCODE = P.HCODE
                                                                LEFT JOIN YDTJ_PEISDETAILRECORD D ON P.ID = D.PROJECTID AND D.DQCODE = P.DQCODE  AND D.HCODE = P.HCODE
                                                                LEFT JOIN YDTJ_TJ_TJXMB M ON D.PEISITEM = M.TJXM  AND D.DQCODE = M.DQCODE  AND D.HCODE = M.HCODE
                                                                 LEFT JOIN YDTJ_PACSJGB N ON P.PEISITEMCODE = N.ZHXMBH  AND N.DJLSH = T.DJLSH  AND N.DQCODE = T.DQCODE  AND N.HCODE = T.HCODE
                                                                 WHERE  1=1 and   P.PEISCODE ='{0}'
                                                                  AND P.TJCS = '{1}'
                                                                  AND P.ISOVER = 1
                                                                  AND Z.HZPROJECT <> '0010'
                                                                  AND N.TPLJ IS NULL
                                                                order by  P.PEISITEMCODE,B_DISP_ORDER,Z.SHOWORDER,M_DISP_ORDER
                                                                  ) X ON T.TJBH = X.PEISCODE  AND T.TJCS = X.TJCS    AND T.DQCODE = X.DQCODE   AND T.HCODE = X.HCODE
                                                                left join YDTJ_TJ_JWS z on t.dqcode=z.dqcode and t.hcode=z.hcode and t.djlsh=z.djlsh
                                                                 left join  YDTJ_TJ_DWTJYDDJB y on t.tjbh=y.tjbh and t.tjcs=y.tjcs and t.dqcode=y.dqcode and t.hcode=y.hcode
                                                                 left join YDTJ_MARITALSTATUS ym on y.hyzk=ym.code and y.dqcode=ym.dqcode and y.hcode=ym.hcode
                                                                left join YDTJ_HELP h1 on t.JCYHYS=h1.listid and t.dqcode=h1.dqcode and t.hcode=h1.hcode and h1.sortid='5'
                                                                left join YDTJ_HELP h2 on z.xy_xyqk=h2.listid and z.dqcode=h2.dqcode and z.hcode=h2.hcode and h2.sortid='7'
                                                                left join YDTJ_HELP h3 on z.yj_yjqk=h3.listid and z.dqcode=h3.dqcode and z.hcode=h3.hcode and h3.sortid='8'
                                                                left join YDTJ_HELP h4 on z.jw_jbname=h4.listid and z.dqcode=h4.dqcode and z.hcode=h4.hcode and h4.sortid='13'
                                                                left join YDTJ_HELP h5 on t.DUTYSTATE=h5.listid and t.dqcode=h5.dqcode and t.hcode=h5.hcode and h5.sortid='3'
                                                                 WHERE 1=1 and T.TJBH ='{0}'
                                                                   AND T.TJCS ='{1}'", v_tjbh, v_tjcs);
            string SQL2 = string.Format(@"select z.zy_startend,z.ZY_UNITNAME,z.ZY_DEPT,z.ZY_GZ,z.ZY_YHYS,z.ZY_FHCS,t.tjbh,t.tjcs from ydtj_tj_tjdjb t
                                        left join YDTJ_TJ_ZYS z on t.dqcode=z.dqcode and t.hcode=z.hcode and t.djlsh=z.djlsh
                                        where 1=1 and  t.tjbh='{0}' and t.tjcs='{1}' ", v_tjbh, v_tjcs);

            ArrayList QueryList = new ArrayList();
            QueryList.Add(new ReportQueryItem(SQL, "Table1"));
            QueryList.Add(new ReportQueryItem(SQL2, "Table2"));
            return MyDbReportData.TextFromMultiSQL(QueryList);
           
        }

        /// <summary>
        /// add by zfx 南煤体检报告sql
        /// </summary>
        /// <param name="v_tjbh"></param>
        /// <param name="v_tjcs"></param>
        /// <returns></returns>
        public static string FillNMTjbb(string v_tjbh, string v_tjcs)
        {
            return MyDbReportData.TextFromOneSQL(string.Format(@"SELECT T.XM, G.GNAME,T.NL,T.TJBH,H.MC,T.ZS,T.JY,J.XM AS GDYS,T.GDRQ,T.DJLSH,T.REPORTTAPE,T.RYBH,T.TJLB,X.NAME,X.JCRQ,X.JCYS,X.XMMC,X.RESULT,X.UNIT,X.CKZ,X.REMIND,X.DECTCTIONFINDING,t.DEPART as 部门,
                                                                to_char(t.tjrq,'yyyy')||'年'||to_char(t.tjrq,'mm')||'月'||to_char(t.tjrq,'dd')||'日' as 体检日期
                                                                  FROM YDTJ_TJ_TJDJB T
                                                                  LEFT JOIN YDTJ_GENDER G ON G.GCODE = T.XB AND T.DQCODE = G.DQCODE  AND T.HCODE = G.HCODE
                                                                  LEFT JOIN YDTJ_HYDWDMB H ON T.DWBH = H.BH  AND T.DQCODE = H.DQCODE  AND T.HCODE = H.HCODE
                                                                  LEFT JOIN YDTJ_GZRY J ON J.GKHM = T.GDYS  AND T.DQCODE = J.DQCODE  AND T.HCODE = J.HCODE
                                                                  LEFT JOIN (SELECT DISTINCT B.MC AS DEPART,Z.NAME,R.XM AS JCYS, P.DETECTIONDATE AS JCRQ, TO_CHAR(P.DECTCTIONFINDING) AS DECTCTIONFINDING,P.PEISITEMCODE AS ZHXMBH, M.MC AS XMMC, TO_CHAR(D.RESULT) AS RESULT, M.DW AS UNIT,
                                                                CASE NVL(RTRIM(LTRIM(D.CKZ)), '')
                                                                WHEN '' THEN
                                                                 (CASE T.XB
                                                                WHEN '1' THEN
                                                                 (CASE NVL(M.NXCKXX,'')
                                                                 WHEN '' THEN
                                                                 M.CKXX || '--' || M.CKSX
                                                                 ELSE
                                                                 M.NXCKXX || '--' || M.NXCKSX
                                                                END) ELSE(CASE NVL(M.CKXX, '')
                                                                WHEN '' THEN
                                                                 ''
                                                                ELSE
                                                                M.CKXX || '--' || M.CKSX
                                                                END) END) ELSE D.CKZ END AS CKZ,
                                                                 D.REMIND,B.DISP_ORDER as B_DISP_ORDER,Z.SHOWORDER,M.DISP_ORDER as M_DISP_ORDER,P.PEISITEMCODE,M.DISP_ORDER,P.DQCODE,P.HCODE,P.PEISCODE,P.TJCS
                                                                FROM YDTJ_PEISRECORD P
                                                                 INNER JOIN YDTJ_TJ_TJDJB T ON P.PEISCODE = T.TJBH  AND P.TJCS = T.TJCS AND T.DQCODE = P.DQCODE  AND T.HCODE = P.HCODE
                                                                LEFT JOIN YDTJ_TJ_TJLXB B ON P.DEPARTMENTCODE = B.LXBH   AND B.DQCODE = P.DQCODE  AND B.HCODE = P.HCODE
                                                                 LEFT JOIN YDTJ_ZHXM_HD Z ON P.PEISITEMCODE = Z.GROUPNO AND Z.DQCODE = P.DQCODE  AND Z.HCODE = P.HCODE
                                                                 LEFT JOIN YDTJ_GZRY R ON P.DETECTIONDOCTOR = R.GKHM  AND R.DQCODE = P.DQCODE  AND R.HCODE = P.HCODE
                                                                LEFT JOIN YDTJ_PEISDETAILRECORD D ON P.ID = D.PROJECTID AND D.DQCODE = P.DQCODE  AND D.HCODE = P.HCODE
                                                                LEFT JOIN YDTJ_TJ_TJXMB M ON D.PEISITEM = M.TJXM  AND D.DQCODE = M.DQCODE  AND D.HCODE = M.HCODE
                                                                 LEFT JOIN YDTJ_PACSJGB N ON P.PEISITEMCODE = N.ZHXMBH  AND N.DJLSH = T.DJLSH  AND N.DQCODE = T.DQCODE  AND N.HCODE = T.HCODE
                                                                 WHERE   1=1 and  P.PEISCODE ='{0}'
                                                                  AND P.TJCS = '{1}'
                                                                  AND P.ISOVER = 1
                                                                  AND Z.HZPROJECT <> '0010'
                                                                  AND N.TPLJ IS NULL
                                                                order by  P.PEISITEMCODE,B_DISP_ORDER,Z.SHOWORDER,M_DISP_ORDER
                                                                  ) X ON T.TJBH = X.PEISCODE  AND T.TJCS = X.TJCS    AND T.DQCODE = X.DQCODE   AND T.HCODE = X.HCODE
                                                                 WHERE 1=1 and T.TJBH ='{0}'
                                                                   AND T.TJCS ='{1}'
                                                                order by  X.PEISITEMCODE", v_tjbh, v_tjcs));
        }
        #endregion


        #region 根据 HTTP 请求中的参数生成报表数据，主要是为例子报表自动分配合适的数据生成函数
        /// <summary>
        /// 为了避免 switch 语句的使用，建立数据名称与数据函数的映射(map)
        /// 在 Global.asax 中创建映射，即在WEB服务启动时初始化映射数据
        /// </summary>

        //简单无参数报表数据的名称与函数映射表
        private delegate string SimpleDataFun();
        private static Dictionary<string, SimpleDataFun> SimpleDataFunMap;

        //有参数报表数据的名称与函数映射表，参数来自 HttpRequest
        private delegate string SpecialDataFun(HttpRequest Request);
        private static Dictionary<string, SpecialDataFun> SpecialDataFunMap;

        public static string BuildByHttpRequest(HttpRequest Request)
        {
            string DataText;
            string DataName = Request.QueryString["data"];

            Trace.Assert(SimpleDataFunMap.Count > 0, "DataFunMap isn't initialized!");

            if (DataName != null && DataName != "")
            {
                //根据数据名称查找映射表，如果找到，执行对应的报表数据函数获取数据
                SimpleDataFun simpleFun;
                SpecialDataFun specialFun;
                if (SimpleDataFunMap.TryGetValue(DataName, out simpleFun))
                {
                    DataText = simpleFun();
                }
                else if (SpecialDataFunMap.TryGetValue(DataName, out specialFun))
                {
                    DataText = specialFun(Request);
                }
                else
                {
                    throw new Exception(string.Format("没有为报表数据 '{0}' 分配处理程序！", DataName));
                }
            }
            else
            {
                string QuerySQL = Request.QueryString["QuerySQL"];
                if (QuerySQL != null)
                {
                    //根据传递的 HTTP 请求中的查询SQL获取数据
                    DataText = DataTextProvider.Build(QuerySQL);
                }
                else if (Request.TotalBytes > 0)
                {
                    //从客户端发送的数据包中获取报表查询参数，URL有长度限制，当要传递的参数数据量比较大时，应该采用这样的方式
                    //这里演示了用这样的方式传递一个超长查询SQL语句。
                    byte[] FormData = Request.BinaryRead(Request.TotalBytes);
                    UTF8Encoding Unicode = new UTF8Encoding();
                    int charCount = Unicode.GetCharCount(FormData, 0, Request.TotalBytes);
                    char[] chars = new Char[charCount];
                    int charsDecodedCount = Unicode.GetChars(FormData, 0, Request.TotalBytes, chars, 0);

                    QuerySQL = new String(chars);

                    DataText = DataTextProvider.Build(QuerySQL);
                }
                else
                {
                    DataText = "";
                }
            }

            return DataText;
        }

        //初始化映射表(map)，在 Global.asax 中被调用
        public static void InitDataFunMap()
        {
            Trace.Assert(SimpleDataFunMap == null && SpecialDataFunMap == null, "DataFunMap already initialized!");
            SimpleDataFunMap = new Dictionary<string, SimpleDataFun>();
            SpecialDataFunMap = new Dictionary<string, SpecialDataFun>();

            SimpleDataFunMap.Add("AppendBlankRow", DataTextProvider.AppendBlankRow);
            SimpleDataFunMap.Add("Categories", DataTextProvider.Categories);
            SimpleDataFunMap.Add("Chart_8b", DataTextProvider.Chart_8b);
            SimpleDataFunMap.Add("Chart_8d", DataTextProvider.Chart_8d);
            SimpleDataFunMap.Add("ContractOne", DataTextProvider.ContractOne);
            SimpleDataFunMap.Add("CrossTab", DataTextProvider.CrossTab);
            SimpleDataFunMap.Add("CrossTabByDay", DataTextProvider.CrossTabByDay);
            SimpleDataFunMap.Add("CrossTabByMonth", DataTextProvider.CrossTabByMonth);
            SimpleDataFunMap.Add("CrossTabCalendar", DataTextProvider.CrossTabCalendar);
            SimpleDataFunMap.Add("CrossTabSubtotal", DataTextProvider.CrossTabSubtotal);
            SimpleDataFunMap.Add("CrossTabYearMonth", DataTextProvider.CrossTabYearMonth);
            SimpleDataFunMap.Add("Customer", DataTextProvider.Customer);
            SimpleDataFunMap.Add("EmployeeOne", DataTextProvider.EmployeeOne);
            SimpleDataFunMap.Add("FreeGridwithDetailGrid", DataTextProvider.FreeGridwithDetailGrid);
            SimpleDataFunMap.Add("InvoiceMany", DataTextProvider.InvoiceMany);
            SimpleDataFunMap.Add("InvoiceOne", DataTextProvider.InvoiceOne);
            SimpleDataFunMap.Add("Picture", DataTextProvider.Picture);
            SimpleDataFunMap.Add("RTFSample", DataTextProvider.RTFSample);
            SimpleDataFunMap.Add("SaleByProduct", DataTextProvider.SaleByProduct);
            SimpleDataFunMap.Add("SaleDetail", DataTextProvider.SaleDetail);
            SimpleDataFunMap.Add("SaleSumByProduct", DataTextProvider.SaleSumByProduct);
            SimpleDataFunMap.Add("SubReport_4a", DataTextProvider.SubReport_4a);
            SimpleDataFunMap.Add("SubReport_4b", DataTextProvider.SubReport_4b);
            SimpleDataFunMap.Add("SubReport_4c", DataTextProvider.SubReport_4c);

            SpecialDataFunMap.Add("Report_7_3g", DataTextProvider.Report_7_3g);
            SpecialDataFunMap.Add("SubReport_4d", DataTextProvider.SubReport_4d);
            SpecialDataFunMap.Add("FilterSaleSummary", DataTextProvider.FilterSaleSummary);
            SpecialDataFunMap.Add("FilterSaleDetail", DataTextProvider.FilterSaleDetail);
            SpecialDataFunMap.Add("FilterCustomerProductCrosstab", DataTextProvider.FilterCustomerProductCrosstab);
            SpecialDataFunMap.Add("FilterCrossPeriod", DataTextProvider.FilterCrossPeriod);
            SpecialDataFunMap.Add("FilterChartBar", DataTextProvider.FilterChartBar);
            SpecialDataFunMap.Add("TestSql", DataTextProvider.TestSql);

        }

        public static string SubReport_4d(HttpRequest Request)
        {
            return DataTextProvider.SubReport_4d(Request.QueryString["city"]);
        }

        public static string Report_7_3g(HttpRequest Request)
        {
            return DataTextProvider.Report_7_3g(Convert.ToInt32(Request.QueryString["BeginNo"]), Convert.ToInt32(Request.QueryString["EndNo"]));
        }

        public static string FilterSaleSummary(HttpRequest Request)
        {
            return DataTextProvider.FilterSaleSummary(Request.QueryString["BeginDate"], Request.QueryString["EndDate"]);
        }

        public static string FilterCustomerProductCrosstab(HttpRequest Request)
        {
            return DataTextProvider.FilterCustomerProductCrosstab(Request.QueryString["BeginDate"], Request.QueryString["EndDate"]);
        }

        public static string FilterCrossPeriod(HttpRequest Request)
        {
            return DataTextProvider.FilterCrossPeriod(Request.QueryString["BeginDate"], Request.QueryString["EndDate"]);
        }
        public static string FilterChartBar(HttpRequest Request)
        {
            return DataTextProvider.FilterChartBar(Request.QueryString["BeginDate"], Request.QueryString["EndDate"]);
        }

        public static string FilterSaleDetail(HttpRequest Request)
        {
            return DataTextProvider.FilterSaleDetail(Request.QueryString["BeginDate"], Request.QueryString["EndDate"],
                Request.QueryString["CategoryID"], Request.QueryString["ProductID"],
                Request.QueryString["City"], Request.QueryString["CompanyName"]);
        }

        public static string TestSql(HttpRequest Request)
        {
            return DataTextProvider.FilterChartBar(Request.QueryString["v_tjbh"], Request.QueryString["v_tjcs"]);
        }
        #endregion
    }
}